Working with Files

Visual Basic has always included many powerful commands for dealing with text and binary files. While Visual Basic 6 hasn't extended the set of built-in functions, it has nonetheless indirectly extended the potential of the language by adding a new and interesting FileSystemObject object that makes it very easy to deal with files and directories. In this section, I provide an overview of all the VBA functions and statements related to files, with many useful tips so that you can get as much as you can from them and stay away from the most recurrent problems.

In general, you can't do many things to a file without opening it. Visual Basic lets you delete a file (using the Kill command), move or rename it (using the Name ... As command), and copy it elsewhere (using the FileCopy command):

' All file operations should be protected against errors.
' None of these functions works on open files.
On Error Resume Next
' Rename a file--note that you must specify the path in the target,
' otherwise the file will be moved to the current directory.
Name "c:\vb6\TempData.tmp" As "c:\vb6\TempData.$$$"
' Move the file to another directory, possibly on another drive.
Name "c:\vb6\TempData.$$$" As "d:\VS98\Temporary.Dat"
' Make a copy of a file--note that you can change the name during the copy
' and that you can omit the filename portion of the target file.
FileCopy "d:\VS98\Temporary.Dat", "d:\temporary.$$$"
' Delete one or more files--Kill also supports wildcards.
Kill "d:\temporary.*"

You can read and modify the attributes of a file using the GetAttr function and the SetAttr command, respectively. The GetAttr function returns a bit-coded value, so you need to test its individual bits using intrinsic constants provided by VBA. Here's a reusable function that builds a descriptive string with all the attributes of the file:

' This routine also works with open files
' and raises an error if the file doesn't exist.
Function GetAttrDescr(filename As String) As String
    Dim result As String, attr As Long
    attr = GetAttr(filename)
    ' GetAttr also works with directories.
    If attr And vbDirectory Then result = result & " Directory"
    If attr And vbReadOnly Then result = result & " ReadOnly"
    If attr And vbHidden Then result = result & " Hidden"
    If attr And vbSystem Then result = result & " System"
    If attr And vbArchive Then result = result & " Archive"
    ' Discard the first (extra) space.
    GetAttrDescr = Mid$(result, 2)
End Function

Similarly, you change the attributes of a file or a directory by passing the SetAttr command a combination of values, as in the following code:

' Mark a file as Archive and Read-only.
filename = "d:\VS98\Temporary.Dat"
SetAttr filename, vbArchive + vbReadOnly
' Change a file from hidden to visible, and vice versa.
SetAttr filename, GetAttr(filename) Xor vbHidden

You can't use the SetAttr function on open files, and of course you can't morph a file into a directory (or vice versa) by flipping the value of the vbDirectory bit. You can determine two more pieces of information about a file without opening it: its length in bytes and its date and time of creation, which you do with the FileLen and FileDateTime functions, respectively.

Print FileLen("d:\VS98\Temporary.Dat")         ' Returns a Long value
Print FileDateTime("d:\VS98\Temporary.Dat")    ' Returns a Date value

You can use the FileLen function against open files too, but in this case you'll retrieve the length that was current before the file was opened.

You can learn the name of the current directory using the CurDir$ function (or its $-less equivalent, CurDir). When this function is passed a drive letter, it returns the current directory on that particular path. In this example, I assume that Microsoft Visual Studio was installed on drive D and that Microsoft Windows NT resides on drive C, but you'll probably get different results on your system:

' Always use On Error--the current dir might be on a removed floppy disk.
On Error Resume Next
Print CurDir$                   ' Displays "D:\VisStudio\VB98"
' The current directory on drive C:
Print = CurDir$("c")            ' Displays "C:\WinNT\System"

You can change both current drive and directory using the ChDrive and ChDir commands, respectively. If you execute a ChDir command on a drive that's not current, you're actually changing the current directory on that drive only, so you must use both commands to ensure you're changing the system's current directory:

' Make "C:\Windows" the current directory. 
On Error Resume Next
SaveCurDir = CurDir$
ChDrive "C:": ChDir "C:\Windows"
' Do whatever you need to do...
' ....
' and then restore the original current directory.
ChDrive SaveCurDir: ChDir SaveCurDir

You can also create and remove subdirectories using the MkDir and RmDir commands, respectively:

' Create a new folder in the current directory, and then make it current.
On Error Resume Next
MkDir "TempDir"
ChDir CurDir$ & "\TempDir"      ' (Assumes current dir is not the root)
' Do whatever you need to do...
' ....
' then restore the original directory and delete the temporary folder.
' You can't remove directories with files in them.
Kill "*.*"                      ' No need for absolute path.
ChDir ".."                      ' Move to the parent directory.
RmDir CurDir$ & "\TempDir"      ' Remove the temporary directory.

You can rename a directory using the Name command, but you can't move a directory elsewhere:

' Assumes that "TempDir" is a subdirectory of the current directory
Name "TempDir" As "TempXXX"

The VBA's Dir function offers a primitive but effective way to iterate over all the files in a directory. You start by calling the Dir function with a filespec argument (which can include wildcards) and an optional argument that specifies the attributes of the files you're interested in. Then at each iteration, you call Dir without any argument until it returns an empty string. The following routine returns an array of filenames in a given directory and also demonstrates the correct way to set up the loop:

Function GetFiles(filespec As String, Optional Attributes As _
    VbFileAttribute) As String()
    Dim result() As String
    Dim filename As String, count As Long, path2 As String
    Const ALLOC_CHUNK = 50
    ReDim result(0 To ALLOC_CHUNK) As String
    filename = Dir$(filespec, Attributes)
    Do While Len(filename)
        count = count + 1
        If count > UBound(result) Then
            ' Resize the result array if necessary.
            ReDim Preserve result(0 To count + ALLOC_CHUNK) As String
        End If
        result(count) = filename
        ' Get ready for the next iteration.
        filename = Dir$
    Loop
    ' Trim the result array.
    ReDim Preserve result(0 To count) As String
    GetFiles = result
End Function

TIP
You can also use the Dir$ function to indirectly test for the existence of a file or a directory, using the following functions:
Function FileExists(filename As String) As Boolean
    On Error Resume Next
    FileExists = (Dir$(filename) <> "")
End Function
Function DirExists(path As String) As Boolean
    On Error Resume Next
    DirExists = (Dir$(path & "\nul") <> "")
End Function
While the code in FileExists is rather straightforward, you might be puzzled by DirExists: where does that "\nul" string come from? The explanation dates back to MS-DOS days and its special filenames "nul", "con", and so on. These names actually refer to special devices (the null device, the console device, and so on) that appear in any directory you search, provided that the directory actually exists. This approach works with any directory, whereas using Dir$("*.*") would fail when you're testing the existence of empty directories.

The GetFiles routine can be used to load a bunch of filenames into a ComboBox control. This is particularly effective if you set the control's Sorted property to True:

Dim Files() As String, i As Long
' All files in C:\WINDOWS\SYSTEM directory, including system/hidden ones.
Files() = GetFiles("C:\windows\system\*.*", vbNormal + vbHidden _
    + vbSystem)
Print "Found " & UBound(Files) & " files."
For i = 1 To UBound(Files)
    Combo1.AddItem Files(i)
Next

If you include the vbDirectory bit in the Attribute argument, the Dir$ function also returns the names of the directories in its results. You can use this feature to create a GetDirectories function that returns the names of all the subdirectories in a given path:

Function GetDirectories(path As String, Optional Attributes As _
    VbFileAttribute, Optional IncludePath As Boolean) As String()
    Dim result() As String
    Dim dirname As String, count As Long, path2 As String
    Const ALLOC_CHUNK = 50
    ReDim result(ALLOC_CHUNK) As String
    ' Build the path name + backslash.
    path2 = path
    If Right$(path2, 1) <> "\" Then path2 = path2 & "\"
    dirname = Dir$(path2 & "*.*", vbDirectory Or Attributes)
    Do While Len(dirname)
        If dirname = "." Or dirname = ".." Then
            ' Exclude the "." and ".." entries.
        ElseIf (GetAttr(path2 & dirname) And vbDirectory) = 0 Then
            ' This is a regular file.
        Else
            ' This is a directory.
            count = count + 1
            If count > UBound(result) Then
                ' Resize the result array if necessary.
                ReDim Preserve result(count + ALLOC_CHUNK) As String
            End If
            ' Include the path if requested.
            If IncludePath Then dirname = path2 & dirname
            result(count) = dirname
        End If
        dirname = Dir$
    Loop
    ' Trim the result array.
    ReDim Preserve result(count) As String
    GetDirectories = result
End Function

A common programming task is to process all files in a directory tree. Thanks to the routines I just listed and the ability to create recursive routines, this becomes (almost) child's play:

' Load the names of all executable files in a directory tree into a ListBox.
' Note: this is a recursive routine.
Sub ListExecutableFiles(ByVal path As String, lst As ListBox)
    Dim names() As String, i As Long, j As Integer
    ' Ensure that there is a trailing backslash.
    If Right(path, 1) <> "\" Then path = path & "\"
    ' Get the list of executable files.
    For j = 1 To 3
        ' At each iteration search for a different extension.
        names() = GetFiles(path & "*." & Choose(j, "exe", "bat", "com"))
        ' Load partial results in the ListBox lst.
        For i = 1 To UBound(names)
            lst.AddItem path & names(i)
        Next
    Next
    ' Get the list of subdirectories, including hidden ones,
    ' and call this routine recursively on all of them.
    names() = GetDirectories(path, vbHidden)
    For i = 1 To UBound(names)
        ListExecutableFiles path & names(i), lst
    Next
End Sub

Text files are the simplest type of files to process. You open them using the Open statement with the For Input, For Output, or For Appending clause, and then start reading data from them or writing data to them. To open a file—either text or a binary file—you need a file number, as in the following code:

Within an individual application, you're usually able to assign unique file numbers to the different routines that deal with files. However, this approach severely hinders code reusability, so I suggest that you use the FreeFile function and query Visual Basic about the first available file number:

After you open a text file for input, you usually read it one line of text at a time using the Line Input statement until the EOF (End-Of-File) function returns True. Any file routine must also take errors into account, both when it opens the file and when it reads its contents. But you can often do a better job if you use the LOF function to determine the length of the file and read all characters in one operation with the Input$ function. Here's a reusable routine that uses this optimized approach:

Function ReadTextFileContents(filename As String) As String
    Dim fnum As Integer, isOpen As Boolean
    On Error GoTo Error_Handler
    ' Get the next free file number.
    fnum = FreeFile()
    Open filename For Input As #fnum
    ' If execution flow got here, the file has been open without error.
    isOpen = True
    ' Read the entire contents in one single operation.
    ReadTextFileContents = Input(LOF(fnum), fnum)
    ' Intentionally flow into the error handler to close the file.
Error_Handler:
    ' Raise the error (if any), but first close the file.
    If isOpen Then Close #fnum
    If Err Then Err.Raise Err.Number, , Err.Description
End Function

' Load a text file into a TextBox control.
Text1.Text = ReadTextFileContents("c:\bootlog.txt")

When you want to write data to a file, you open the file using the For Output clause if you want to replace the current contents or the For Append clause to simply append new data to the file. You usually send output to this output file with a series of Print # statements, but it's much faster if you gather your output in a string and print that instead. Here's a reusable function that does it all for you:

Sub WriteTextFileContents(Text As String, filename As String, _
    Optional AppendMode As Boolean)
    Dim fnum As Integer, isOpen As Boolean
    On Error GoTo Error_Handler
    ' Get the next free file number.
    fnum = FreeFile()
    If AppendMode Then
         Open filename For Append As #fnum
     Else
         Open filename For Output As #fnum
     End If
     ' If execution flow gets here, the file has been opened correctly.
     isOpen = True
     ' Print to the file in one single operation.
     Print #fnum, Text
     ' Intentionally flow into the error handler to close the file.
Error_Handler:
    ' Raise the error (if any), but first close the file.
    If isOpen Then Close #fnum
    If Err Then Err.Raise Err.Number, , Err.Description
End Sub

Even if Visual Basic 6 didn't add any function specifically intended to work with text files, its new Split function turns out to be extremely useful for text processing. Let's say that your text file contains items to be loaded into a ListBox or ComboBox control. You can't use the ReadTextFileContents routine that I showed you previously to load it directly in the control, but you can use it to make your code more concise:

Sub TextFileToListbox(lst As ListBox, filename As String)
    Dim items() As String, i As Long
    ' Read the file's contents, and split it into an array of strings.
    ' (Exit here if any error occurs.)
    items() = Split(ReadTextFileContents(filename), vbCrLf)
    ' Load all non-empty items into the ListBox.
    For i = LBound(items) To UBound(items)
        If Len(items(i)) > 0 Then lst.AddItem items(i)
    Next
End Sub

Delimited text files contain multiple fields in each line of text. Even if no serious programmer would ever use delimited text files as the primary means to store an application's data, these files nevertheless play an important role because they offer a great way to exchange data between different database formats. For example, delimited text files are often the only viable way to import and export data to mainframe databases. Here's the structure of a simple semicolon-delimited text file. (Note that it's customary for the first line of the file to hold the field's names.)

Name;Department;Salary
John Smith;Marketing;80000
Anne Lipton;Sales;75000
Robert Douglas;Administration;70000

Taken together, the Split and the Join functions are especially useful for importing and exporting delimited text files. For example, see how easy it is to import the contents of a semicolon-delimited data file into an array of arrays:

' The contents of a delimited text file as an array of strings arrays
' NOTE: requires the GetTextFileLines routine
Function ImportDelimitedFile(filename As String, _
    Optional delimiter As String = vbTab) As Variant()
    Dim lines() As String, i As Long
    ' Get all lines in the file.
    lines() = Split(ReadTextFileContents(filename), vbCrLf)
    ' To quickly delete all empty lines, load them with a special char.
    For i = 0 To UBound(lines)
        If Len(lines(i)) = 0 Then lines(i) = vbNullChar
    Next
    ' Then use the Filter function to delete these lines.
    lines() = Filter(lines(), vbNullChar, False)
    ' Create a string array out of each line of text
    ' and store it in a Variant element.
    ReDim values(0 To UBound(lines)) As Variant
    For i = 0 To UBound(lines)
        values(i) = Split(lines(i), delimiter)
    Next
    ImportDelimitedFile = values()
End Function

' An example of using the ImportDelimitedFile routine
Dim values() As Variant, i As Long
values() = ImportDelimitedFile("c:\datafile.txt", ";")
' Values(0)(n) is the name of the Nth field.
' Values(i)(n) is the value of the Nth field on the ith record.
' For example, see how you can increment employees' salaries by 20%.
For i = 1 to UBound(values)
    values(i)(2) = values(i)(2) * 1.2
Next

Using an array of arrays is a particularly good strategy because it makes it easy to add new records:

' Add a new record.
ReDim Preserve values(0 To UBound(values) + 1) As Variant
values(UBound(values)) = Split("Roscoe Powell;Sales;80000", ";")

' Delete the Nth record
For i = n To UBound(values) - 1
    values(i) = values(i + 1)
Next
ReDim Preserve values(0 To UBound(values) _ 1) As Variant

Writing an array of string arrays back to a delimited file is also a simple task, thanks to this reusable routine that builds on the Join function:

' Write the contents of an array of string arrays to a delimited
' text file.
' NOTE: requires the WriteTextFileContents routine
Sub ExportDelimitedFile(values() As Variant, filename As String, _
    Optional delimiter As String = vbTab)
    Dim i As Long
    ' Rebuild the individual lines of text of the file.
    ReDim lines(0 To UBound(values)) As String
    For i = 0 To UBound(values)
        lines(i) = Join(values(i), delimiter)
    Next
    ' Create CRLFs among records, and write them.
    WriteTextFileContents Join(lines, vbCrLf), filename
End Sub

' Write the modified data back to the delimited file.
ExportDelimitedFile values(), "C:\datafile.txt", ";"

All the routines described in this section rely on the assumption that the delimited text file is small enough to be held in memory. While this might sound like a serious limitation, in practice text files are mostly used to create small archives or to move small quantities of data between different database formats. If you find that you're having problems because of the size of the array, you need to read and write it in chunks using multiple Line Input # and Print # statements. In most cases, you can deal with files up to 1 or 2 megabytes in size (or even more, depending on how much RAM memory you have) without any problem.

To open a binary file, you use the Open statement with the For Random or For Binary options. Let me first explain the latter mode, which is the simpler of the two. In Binary mode, you write to file using the Put statement and read data back with the Get statement. Visual Basic determines how many bytes are written or read by looking at the structure of the variable you pass as the last argument:

Dim numEls As Long, text As String
numEls = 12345: text = "A 16-char string"
' Binary files are automatically created if necessary.
Open "data.bin" For Binary As #1
Put #1, , numEls            ' Put writes 4 bytes.
Put #1, , text              ' Put writes 16 bytes (ANSI format).

When reading data back, you must repeat the same sequence of statements but it's up to you to correctly dimension variable length strings. You don't need to close and reopen a binary file because you can use the Seek statement to reposition the file pointer to a specific byte:

Seek #1, 1                  ' Back to the beginning (first byte is byte 1)
Get #1, , numEls            ' All Long values are 4 bytes.
text = Space$(16)           ' Prepare to read 16 bytes.
Get #1, , text              ' Do it.

Alternatively, you can move the file pointer right before writing or reading data using a second argument, as in this code:

CAUTION
When you open a binary file, Visual Basic automatically creates it if it doesn't exist. Therefore, you can't use an On Error statement to determine whether the file exists already. In this case, use the Dir$ function to ascertain that the file actually exists before opening it.

You can quickly write an entire array to disk and read it back in one single operation; but because you must correctly dimension the array before reading it, you'll also have to prefix the data with the number of actual elements, in most cases:

' Store a zero-based array of Double.
Put #1, 1, CLng(UBound(arr)) ' First store the UBound value.
Put #1, , arr()              ' Then store all items in one shot.
' read it back
Dim LastItem As Long 
Get #1, 1, LastItem          ' Read the number of items.
ReDim arr2(0 To LastItem) As Double
Get #1, , arr2()             ' Read the array in memory in one operation.
Close #1

CAUTION
If you read data back using a read sequence different from the original write sequence, you'll read wrong data into your variables. In some cases, this mistake might cause the Visual Basic environment to crash when trying to display the contents of those variables. For this reason, always double-check the order of write and read operations. When in doubt, save your work before running the code.

When you're reading from a binary file, you can't use the EOF function to find out when you're at the end of the data; instead, you should test the value returned by the LOF function (the length of the file in bytes) and use the Seek function to determine when you have read all the data in it:

CAUTION
When storing strings to disk—either to text or binary files—Visual Basic automatically converts them from Unicode to ANSI, which saves a noticeable amount of disk space and lets you exchange data with 16-bit Visual Basic applications. If you're writing Unicode-aware programs for the international market, however, this behavior gets in the way and can cause loss of data because the string you're reading back from a file won't necessarily match the one you had stored previously. To fix a problem, you have to move the string into a Byte array and save that instead:
Dim v As Variant, s As String, b() As Byte
s = "This is a string that you want to save in Unicode format"
b() = s: v = b()     ' You need this double step.
Put #1, , v          ' Write that to disk.

' Read it back.
Get #1, 1, v: s = v  ' No need for intermediary Byte array here.

Opening a binary file using the For Random clause differs from what I have illustrated so far in a number of important respects:

Strings stored to binary files opened with the For Random clause are prefixed by a 2-byte value that indicates the number of characters that follow. This means that you can't write a string that contains more than 32,767 characters, which is also the largest valid record size. To write a longer string, you should use the For Binary clause.

One final note: All the code examples seen so far assume that we're working in a single-user environment and don't account for issues such as the errors you get when opening a file already opened by another user, or the capability to lock all or a portion of a data file using the Lock statement (and later unlock it using the Unlock statement). For more information, see the Visual Basic documentation.

TIP
If you don't want to get involved with lots of additional evaluations when writing and reading data in a binary file, you can follow a shorter path using an intermediate Variant variable. If you store a value of any type (other than object) into a Variant variable and then write the variable to a binary file, Visual Basic writes the type of the variable (that is, the VarType return value) and then the data. If the variable holds a string or an array, Visual Basic also stores enough information to read exactly the necessary number of bytes, freeing you from additional read statements:
Dim v As Variant, s(100) As String, i As Long
' Fill the s() array with data... (omitted)
Open "c:\binary.dat" For Binary As #1
v = s()              ' Store the array in a Variant variable,
Put #1, , v          ' and write that to disk.
v = Empty            ' Release memory.

' Read data back.
Dim v2 As Variant, s2() As String
Get #1, 1, v2        ' Read data in the Variant variable,
s2() = v2            ' and then move it to the real array.
v2 = Empty           ' Release memory. 
Close #1
This approach also works with multidimensional arrays.

Visual Basic 6 comes with a new library of file commands, which enables programmers to easily scan drives and directories, perform basic file operations (including copy, delete, move, and so on), and extract information not available through regular Visual Basic functions. But in my opinion, the best feature of the new commands is that you can do all that using a modern, coherent, object-oriented syntax, which makes your code much more readable. All this power is provided in the form of the external FileSystemObject hierarchy, embedded in the Microsoft Scripting Library, the library that also hosts the Dictionary object. (See Chapter 4 for instructions about installing and using this library.) The FileSystemObject hierarchy includes many complex objects (see Figure 5-1), and each object exposes many interesting properties and methods.

At the root of the hierarchy is the FileSystemObject object itself. It exposes many methods and only one property, Drives, which returns the collection of all the drives in the system. The FileSystemObject object (abbreviated as FSO in the following text and code) is the only creatable object in the hierarchy—that is, it's the only object that can be declared using the New keyword. All the other objects are dependent objects that derive from this one and are exposed in the form of methods or properties. See how easy it is to fill an array with the list of all the ready drives in the system and their capacities:

Dim fso As New Scripting.FileSystemObject, dr As Scripting.Drive
On Error Resume Next        ' Needed for not-ready drives
For Each dr In fso.Drives
    Print dr.DriveLetter & " [" & dr.TotalSize & "]"
Next

Table 5-3 below summarizes the many methods exposed by the FSO object. A few of them are also available (often with different names and syntax) as methods of secondary Folder and File objects. Most of these methods add functionality to commands already present in Visual Basic. For example, you can delete non-empty folders (be very careful!) and copy and rename multiple files and directories with one single command. You can also easily extract portions of a filename without having to write special routines.

Syntax	Description
BuildPath (Path, Name)	Returns a complete filename, obtained by concatenating the path (relative or absolute) and name.
CopyFile Source, Destination, [Overwrite]	Copies one or more files: Source can include wildcards, and Destination is considered to be a directory if it ends with a backslash. It overwrites existing files unless you set Overwrite to False.
CopyFolder Source, Destination, [Overwrite]	Same as CopyFile, but copies entire folders with their contents (subfolders and files). If Destination doesn't correspond to an existing directory, it's created (but not if Source contains wildcards).
CreateFolder(Path) As Folder	Creates a new Folder object and returns it; raises an error if the folder already exists.
CreateTextFile(FileName, [Overwrite], [Unicode]) As TextStream	Creates a new TextFile object and returns it; set Overwrite = False to avoid overwriting an existing file; set Unicode = True to create a Unicode TextFile object.
DeleteFile FileSpec, [Force]	Deletes one or more files. FileSpec can include wildcards; set Force = True to force the deletion of read-only files.
DeleteFolder(FolderSpec, [Force])	Deletes one or more folders, together with their contents; set Force = True to force the deletion of read-only files.
DriveExists(DriveName)	Returns True if a given logical drive exists.
FileExists(FileName)	Returns True if a given file exists. (The path can be relative to the current directory.)
FolderExists(FolderName)	Returns True if a given folder exists. (The path can be relative to the current directory.)
GetAbsolutePathName(Path)	Converts a path relative to the current directory into an absolute path.
GetBaseName(Filename)	Extract the base filename (without its path and extension); it doesn't check whether the file and/or the path actually exist.
GetDrive(DriveName) As Drive	Returns the Drive object that corresponds to the letter or the UNC path passed as an argument. (It checks that the drive actually exists).
GetDriveName(Path)	Extracts the drive from a path.
GetExtensionName(FileName)	Extracts the extension string from a filename.
GetFile(FileName)	Returns the File object corresponding to the name passed as the argument. (Can be absolute or relative to the current directory.)
GetFileName(	Extract the filename (without its path but with its extension); it doesn't check whether the file and/or the path actually exist.
GetFolder(FolderName) As Folder	Returns the Folder object corresponding to the path passed as the argument. (Can be absolute or relative to the current directory.)
GetParentFolderName(Path)	Returns the name of the parent directory of the directory passed as the argument (or an empty string if the parent directory doesn't exist).
GetSpecialFolder(SpecialFolder) As Folder	Returns a Folder object that corresponds to one of the special Windows directories. SpecialFolder can be 0-WindowsFolder, 1-SystemFolder, 2-TemporaryFolder.
GetTempName()	Returns the name of a nonexistent file that can be used as a temporary file.
MoveFile(Source, Destination)	Same as CopyFile, but it deletes the source file. It can also move among different drives, if this function is supported by the operating system.
MoveFolder(Source, Destination)	Same as MoveFile, but works on directories instead.
OpenTextFile(FileName, [IOMode], [Create], [Format])As TextStream	Opens a text file and returns the corresponding TextStream object. IOMode can be one or a combination (use the OR operator) of the following constants: 1-ForReading, 2-ForWriting, 8-ForAppending; set Create to True if you want to create a new file; Format can be 0-TristateFalse (ANSI), -1-TristateTrue (Unicode) or -2-TristateUseDefault (determined by the system).

This object exposes only properties (no methods), all of which are summarized in Table 5-4. All the properties are read-only, except the VolumeName property. This short code snippet determines the local drives that are ready and have at least 100 MB of free space on them:

Dim fso As New Scripting.FileSystemObject, dr As Scripting.Drive
For Each dr In fso.Drives
    If dr.IsReady Then
        If dr.DriveType = Fixed Or dr.DriveType = Removable Then
            ' 2 ^ 20 equals one megabyte.
            If dr.FreeSpace > 100 * 2 ^ 20 Then
                Print dr.Path & " [" & dr.VolumeName & "] = " _
                    & dr.FreeSpace
            End If
        End If
    End If
Next

Syntax	Description
AvailableSpace	The free space on the drive, in bytes; it usually coincides with the value returned by the FreeSpace property, unless the operating system supports disk quotas.
DriveLetter	The letter associated with the drive or an empty string for network drives not associated with a letter.
DriveType	A constant that indicates the type of the drive: 0-Unknown, 1-Removable, 2-Fixed, 3-Remote, 4-CDRom, 5-RamDisk.
FileSystem	A string that describes the file system in use: FAT, NTFS, CDFS.
FreeSpace	The free space on the drive. (See AvailableSpace.)
IsReady	True if the drive is ready, False otherwise.
Path	The path associated with the drive, without the backslash (for example, C:).
RootFolder	The Folder object that corresponds to the root directory.
SerialNumber	A Long number that corresponds to the serial disk number.
ShareName	The network shared name for the drive or an empty string if it isn't a network drive.
TotalSize	The total capacity of the drive, in bytes.
VolumeName	The disk label (can be read and written).

The Folder object represents an individual subdirectory. You can obtain a reference to such an object in different ways: by using the GetFolder or GetSpecialFolder methods of the FileSystemObject object, through the RootFolder property of a Drive object, through the ParentFolder property of a File object or another Folder object, or by iterating over the SubFolders collection of another Folder object. The Folder object exposes a number of interesting properties (see Table 5-5), but only the Attribute and Name properties can be written to. The most intriguing properties are probably the SubFolders and Files collections, which let you iterate through subdirectories and files using an elegant and concise syntax:

' Print the names of all first-level directories on all drives
' together with their short 8.3 names.
Dim fso As New Scripting.FileSystemObject
Dim dr As Scripting.Drive, fld As Scripting.Folder
On Error Resume Next
For Each dr In fso.Drives
    If dr.IsReady Then
        Print dr.RootFolder.Path       ' The root folder.
        For Each fld In dr.RootFolder.SubFolders
            Print fld.Path & " [" & fld.ShortName & "]"
        Next
    End If
Next

Syntax	Description	Applies To
Attributes	The attributes of the file or the folder, as a combination of the following constants: 0- Normal, 1-ReadOnly, 2-Hidden, 4-System, 8-Volume, 16-Directory, 32-Archive, 64-Alias, 2048-Compressed. The attributes Volume, Directory, Alias, and Compressed can't be modified.	Folder and File
DateCreated	Creation date (a read-only Date value).	Folder and File
DateLastAccessed	The date of the last access (a read-only Date value).	Folder and File
DateLastModified	The date of the last modification (a read-only Date value).	Folder and File
Drive	The Drive object where the file or the folder is located.	Folder and File
Files	The collection of all the contained File objects.	Folder only
IsRootFolder	True if this is the root folder for its drive.	Folder only
Name	The name of the folder or the file. Assign a new value to rename the object.	Folder and File
ParentFolder	The parent Folder object.	Folder and File
Path	The path of the Folder or the File. (This is the default property.)	Folder and File
ShortName	The name of the object in 8.3 MS-DOS format.	Folder and File
ShortPath	The path of the object in 8.3 MS-DOS format.	Folder and File
Size	The size in bytes of a File object; the sum of the size of all contained files and sub folders for a Folder object.	Folder and File
SubFolders	The collection of all the subfolders contained in this folder, including system and hidden ones.	Folder only
Type	A string description of the object. For example: fso.GetFolder("C:\Recycled").Type returns "Recycle Bin"; for File objects, this value depends on their extensions (for example, "Text Document" for a TXT extension).	Folder and File

The Folder object also exposes a few methods, summarized in Table 5-6. Note that you can often achieve similar results using appropriate methods of the main FSO object. You can also create a new Folder using the Add method applied to the SubFolders collection, as shown in the following recursive routine, which duplicates the directory structure of one drive onto another drive without also copying the contained files:

' Call this routine to initiate the copy process.
' NOTE: the destination folder is created if necessary.
Sub DuplicateDirTree(SourcePath As String, DestPath As String)
    Dim fso As New Scripting.FileSystemObject
    Dim sourceFld As Scripting.Folder, destFld As Scripting.Folder
    ' The source folder must exist.
    Set sourceFld = fso.GetFolder(SourcePath)
    ' The destination folder is created if necessary.
    If fso.FolderExists(DestPath) Then
        Set destFld = fso.GetFolder(DestPath)
    Else
        Set destFld = fso.CreateFolder(DestPath)
    End If
    ' Jump to the recursive routine to do the real job.
    DuplicateDirTreeSub sourceFld, destFld
End Sub

Private Sub DuplicateDirTreeSub(source As Folder, destination As Folder)
    Dim sourceFld As Scripting.Folder, destFld As Scripting.Folder
    For Each sourceFld In source.SubFolders
        ' Copy this subfolder into destination folder.
        Set destFld = destination.SubFolders.Add(sourceFld.Name)
        ' Then repeat the process recursively for all
        ' the subfolders of the folder just considered.
        DuplicateDirTreeSub sourceFld, destFld
    Next
End Sub

Syntax	Description	Applies To
Copy Destination, [OverWriteFiles]	Copy the current File or the Folder object to another path; this is similar to FSO's CopyFolder and CopyFile methods, which are also able to copy multiple objects in one operation.	Folder and File
CreateTextFile(FileName, [Overwrite], [Unicode]) As TextStream	Creates a text file in the current Folder and returns the corresponding TextStream object. See the corresponding FSO's method for an explanation of the individual arguments.	Folder only
Delete [Force]	Delete this File or this Folder object (with all its contained subfolders and files). Similar to FSO's DeleteFile and DeleteFolder methods.	Folder and File
Move DestinationPath	Move this File or Folder object to another path; similar to FSO's MoveFile and MoveFolder methods.	Folder and File
OpenAsTextStream([IOMode], [Format]) As TextStream	Open this File object as a text file and return the corresponding TextStream object.	File only

The File object represents a single file on disk. You can obtain a reference to such an object in two ways: by using the GetFile method of the FSO object or by iterating over the Files collection of its parent Folder object. Despite their different natures, File and Folder objects have many properties and methods in common, so I won't repeat the descriptions that were given in Tables 5-5 and 5-6.

A limitation of the FSO hierarchy is that you have no direct way to filter filenames using wildcards, as you can do with the Dir$ function. All you can do is iterate through the Files collection of a Folder object and test the file's name, extensions, or other attributes to see whether you are interested in it as shown below.

' List all the DLL files in the C:\WINDOWS\SYSTEM directory.
Dim fso As New Scripting.FileSystemObject, fil As Scripting.File
For Each fil In fso.GetSpecialFolder(SystemFolder).Files
    If UCase$(fso.GetExtensionName(fil.Path)) = "DLL"  Then
        Print fil.Name
    End If
Next

The FileSystemObject hierarchy doesn't permit many operations on files. More specifically, while you can list their properties (including many properties that are beyond the current capabilities of native VBA file functions), you can open files only in text mode, as I explain in the next section.

The TextStream object represents a file opened in text mode. You can obtain a reference to such an object in the following ways: by using the CreateTextFile or the OpenTextFile method of the FSO object, by using the CreateTextFile method of a Folder object, or by using the OpenAsTextStream method of a File object. The TextStream object exposes a number of methods and read-only properties, all of which are described in Table 5-7. The TextStream object does offer some new features in addition to regular VBA file commands—for example, the ability to keep track of the current line and column while reading from or writing to the text file. This feature is exploited in this reusable routine that scans all the TXT files in a directory for a search string and returns an array of results (actually, an array of arrays) with all the files that contain that search string as well as the line number and the column number to indicate the position of the string within the file:

' For each TXT file that contains the search string, the function
' returns a Variant element that contains a 3-item array that holds
' the filename, the line number, and the column number.
' NOTE: all searches are case insensitive.
Function SearchTextFiles(path As String, search As String) As Variant()
    Dim fso As New Scripting.FileSystemObject
    Dim fil As Scripting.File, ts As Scripting.TextStream
    Dim pos As Long, count As Long
    ReDim result(50) As Variant

    ' Search for all the TXT files in the directory.    
    For Each fil In fso.GetFolder(path).Files
        If UCase$(fso.GetExtensionName(fil.path)) = "TXT" Then
            ' Get the corresponding TextStream object.
            Set ts = fil.OpenAsTextStream(ForReading)
            ' Read its contents, search the string, close it.
            pos = InStr(1, ts.ReadAll, search, vbTextCompare)
            ts.Close

            If pos > 0 Then
                ' If the string has been found, reopen the file
                ' to determine string position in terms of (line,column).
                Set ts = fil.OpenAsTextStream(ForReading)
                ' Skip all preceding characters to get where 
                ' the search string is.
                ts.Skip pos _ 1 
                ' Fill the result array, make room if necessary.
                count = count + 1
                If count > UBound(result) Then
                    ReDim Preserve result(UBound(result) + 50) As Variant
                End If
                ' Each result item is a 3-element array.
                result(count) = Array(fil.path, ts.Line, ts.Column)
                ' Now we can close the TextStream.
                ts.Close
            End If
        End If
    Next

    ' Resize the result array to indicate number of matches.
    ReDim Preserve result(0 To count) As Variant
    SearchTextFiles = result
End Function

' An example that uses the above routine: search for a name in all
' the TXT files in E:\DOCS directory, show the results in 
' the lstResults ListBox, in the format "filename [line, column]".
Dim v() As Variant, i As Long
v() = SearchTextFiles("E:\docs", "Francesco Balena")
For i = 1 To UBound(v)
    lstResults.AddItem v(i)(0) & " [" & v(i)(1) & "," & v(i)(2) & "]"
Next

Property or Method	Syntax	Description
Property	AtEndOfLine	True if the file pointer is at the end of the current line.
Property	AtEndOfFile	True if the file pointer is at the end of file (similar to VBA's EOF function).
Method	Close	Closes the file (similar to VBA's Close statement).
Property	Column	Current column number.
Property	Line	Current line number.
Method	Read(Characters)	Reads a specified number of characters and returns a string (similar to VBA's Input$ function).
Method	ReadAll()	Reads the entire file into a string (similar to VBA's Input$ function when used with the LOF function).
Method	ReadLine()	Reads the next line of text and returns a string (similar to VBA's Line Input statement).
Method	Skip Characters	Skips over a specified number of characters.
Method	SkipLine	Skips over a line of text.
Method	Write Text	Writes a string of characters, without a trailing Newline character (similar to the Print# command with a trailing semicolon).
Method	WriteBlankLines Lines	Writes the indicated number of blank lines (similar to one or more Print# commands without any argument).
Method	WriteLine [Text]	Writes a string of characters, with a trailing Newline character (similar to the Print# command without a trailing semicolon).

Working with Files

Handling Files

Handling Directories

Iterating Over All Files in a Directory

Processing Text Files

Processing Delimited Text Files

Processing Binary Files

The FileSystemObject Hierarchy

The FileSystemObject root object

The Drive object

The Folder object

The File object

The TextStream object